﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

using Excel = Microsoft.Office.Interop.Excel;
using ExcelLibrary;
using ExcelLibrary.SpreadSheet;

 

using cecbData;
using cecbClasses.Data_Adapters;
using System.Data;
using System.Collections.ObjectModel;
using cecbClasses;
using System.IO;
using System.Diagnostics;

namespace CECB
{
    /// <summary>
    /// Interaction logic for ActivationCodeEmail_Window.xaml
    /// </summary>
    public partial class ActivationCodeEmail_Window : Window
    {
       // Dss.Methods.ActivationCodeEmail em;
        cecbClasses.Data_Adapters.ActivationCodeEmail em;
         

        public ActivationCodeEmail_Window()
        {
            InitializeComponent();
             em = new cecbClasses.Data_Adapters.ActivationCodeEmail();

             TeamAutocompleteTextBox.ItemsSource = em.getProjectDetails();
             buttonSendAllEmail.IsEnabled = false;
             button3.IsEnabled = false;
             
        }

        private void TextBox_selectMemeber_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
          //  TextBox_selectMemeber.ItemsSource = em.getAllMemberNames();
        }

        private void comboBox_project_DropDownOpened(object sender, EventArgs e)
        {
          //  comboBox_project.ItemsSource = em.getAllProjectNames();
        }

        private void comboBox_project_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
        //    Label_projectTeam.Content = em.getTeamRefByProRef(comboBox_project.SelectedValue.ToString());
        }

        private void ReferenceTextBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
           
           
        }
         
        //private void button2_Click(object sender, RoutedEventArgs e)
        //{
        //    this.Cursor = Cursors.Wait;
        //    String from = "sliititb1@gmail.com";
        //    String to = "gayashan.gzn@gmail.com";
        //    String cc = "";
        //    String displayName = "CECB";
        //    String password = "900760922v";
        //    String Subjet = "Informing Activation Code";
        //    String Body = "   Activation Code: ";
            

        //    bool mailSent = new cecbClasses.Data_Adapters.ActivationCodeEmail().genarateEmail(from, to, cc, displayName, password, Subjet, Body);

        //    if (mailSent == true)
        //        MessageBox.Show("Email sent Succesfully");
        //    else
        //        MessageBox.Show("Email sending failed");

        //    this.Cursor = Cursors.Arrow;
        //}

        Projects prj = null;

        private void TeamReferenceTextBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

            try
            {
                if (listBoxComponents.HasItems)
                {
                    listBoxComponents.ItemsSource = null;
                    listBoxComponents.Items.Clear();
                }
                if (listBoxMembers.HasItems)
                {
                    listBoxMembers.ItemsSource = null;
                    listBoxMembers.Items.Clear();
                }

                    Dss.Methods.Slider slid = new Dss.Methods.Slider();
                    prj = (Projects)TeamAutocompleteTextBox.SelectedItem;
                    String pref = prj.proj_reference.ToString();
                    Label_projectTeam.Content = new cecbData.DSSEntities().Projects.First(i => i.proj_reference.Equals(pref)).proj_consultant; // em.getTeamRefByProRef(TeamAutocompleteTextBox.Text);

                Dss.Methods.GenarateExcel xgen = new Dss.Methods.GenarateExcel();

                listBoxMembers.ItemsSource = xgen.getAllMemberNames(Label_projectTeam.Content.ToString());

                cecbData.DSSEntities dataContext = new cecbData.DSSEntities();

                IQueryable<Components> qComponents = (from c in dataContext.ProjectImpacts
                                                      join b in dataContext.Impacts on c.impt_reference equals b.impt_reference
                                                      join d in dataContext.Components on b.impt_component equals d.cmpt_reference
                                                      where c.proj_reference == pref
                                                      orderby b.impt_component
                                                      select d).Distinct();

                listBoxComponents.ItemsSource = qComponents;
            }
            catch (Exception)
            {
                 
            }

            
        }
        private void listBoxMembers_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            cecbData.Members mem = (cecbData.Members)listBoxMembers.SelectedItem;
            textBox1.Text = mem.mem_name.ToString();
            teamMember.Content = mem.mem_name + "  =>" + mem.mem_experiences;

        }

        private void Image_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            this.Close();
        }

        private void button2_Click_1(object sender, RoutedEventArgs e)
        {

        }

        private void buttonExcelGenarate_Click(object sender, RoutedEventArgs e)
        {


            if (listBoxMembers.SelectedIndex > -1)
            {

                try
                {
                    int x = listBoxComponents.SelectedIndex;

                    var itemContainer = listBoxComponents.ItemContainerGenerator.ContainerFromIndex(x) as ListBoxItem;
                    itemContainer.Background = Brushes.Green;

                    // MessageBox.Show(listBoxComponents.SelectedItem.ToString());
                    Components cmp = (Components)listBoxComponents.SelectedItem;
                    // MessageBox.Show(cmp.cmpt_name.ToString());
                    Members mem = (Members)listBoxMembers.SelectedItem;
                    genarateExcell(cmp.cmpt_name, mem);
                    buttonSendAllEmail.IsEnabled = true;
                    button3.IsEnabled = true;
                }
                catch (Exception)
                {

                    MessageBox.Show("Select a component", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
                }

            }
            else
                MessageBox.Show("Select a member", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
        }


        #region =============================== EmailGenaration =====================================================

        string destFile;
        string destFile1;

        Func_Projects ObjProjctsFunc = new Func_Projects();

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        String filePath = "";

        public void genarateExcell(String com , Members mem) {

            try
            {
                Excel.Range oRng;
                Excel.Borders b = null;
                Excel.Borders fb = null;
                Excel.Range range;

                string startColName, endColName;
                int startColNo, endColNo;


                oXL = new Excel.Application();
                 oXL.Visible = false;
               
                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add());
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;


             

                // MessageBox.Show(Convert.ToChar(65).ToString());

                DataAdapter da = new DataAdapter();
                Projects prj = (Projects)TeamAutocompleteTextBox.SelectedItem;
                List<Get_Leopold_Phase_Result> LstResult = ObjProjctsFunc.GetLeopoldPhases(prj.proj_reference);
                ////////////////////////////////////

                


                cecbClasses.Data_Adapters.CreateExcell mm = new cecbClasses.Data_Adapters.CreateExcell();
                List<Activities> lstActivities = mm.getActivity(prj.proj_reference);

                cecbClasses.Data_Adapters.CreateExcell ee = new cecbClasses.Data_Adapters.CreateExcell();
                List<Impacts> lstImpacts = ee.getImpacts(prj.proj_reference);

                cecbClasses.Data_Adapters.CreateExcell jj = new cecbClasses.Data_Adapters.CreateExcell();
                List<Components> lstcmpt = jj.getComponent(prj.proj_reference, com);




                ////////////////////////////////////
                //List<Get_Leopold_Activities_Result> lstActivities = ObjProjctsFunc.GetLeopoldActivities(prj.proj_reference);
                //List<Get_Leopold_Impacts_Result> lstImpacts = ObjProjctsFunc.GetLeopolImpacts(prj.proj_reference);
                List<Get_Leopold_Matric_Result> lstMatrice = ObjProjctsFunc.GetLeopolMatrices(prj.proj_reference);


                // ---- Header Row ---
                oSheet.Cells[3, 1] = "Category";
                oSheet.get_Range("A3").ColumnWidth = 15;
                oSheet.get_Range("A3").Borders.Weight = 1;
                oSheet.get_Range("A3").Borders.LineStyle = 6;


                oSheet.Cells[3, 2] = "Component";
                oSheet.get_Range("B3").ColumnWidth = 15;

                oSheet.Cells[3, 4] = "Project Impact";
                oSheet.get_Range("D3").ColumnWidth = 40;
                oSheet.get_Range("C3", "D3").Merge();

                oSheet.get_Range("A3", "D3").Font.Bold = true;
                oSheet.get_Range("A3", "D3").VerticalAlignment = VerticalAlignment.Center;

                int z = 5;
                startColNo = 69;
                startColName = Convert.ToChar(startColNo).ToString() + "2";

                // --- Phases Headings -----
                foreach (Get_Leopold_Phase_Result oRsts in LstResult)
                {
                    endColNo = startColNo + (((int)oRsts.NoOfActivities) * 2) - 1;
                    endColName = Convert.ToChar(endColNo).ToString() + "2";

                    oSheet.Cells[2, z] = oRsts.actv_phase;

                    oSheet.get_Range(startColName, endColName).Font.Bold = true;

                    oSheet.get_Range(startColName, endColName).Merge();
                    oSheet.get_Range(startColName, endColName).HorizontalAlignment = HorizontalAlignment.Center;
                    oSheet.get_Range(startColName, endColName).Borders.Weight = 1;
                    startColNo = endColNo + 1;
                    startColName = Convert.ToChar(startColNo).ToString() + "2";

                    z = z + (int)oRsts.NoOfActivities * 2;
                    //  DrawCellBorders(2, z, 2, z, true, true, true, true, false);

                }

                //// --- Get Activities ---
                startColNo = 69;
                startColName = Convert.ToChar(startColNo).ToString() + "4";

                int startColNo2 = 69;
                string startColName2 = Convert.ToChar(startColNo2).ToString() + "3";
                int endColNo2;
                string endColName2;

                int ActNo = 1;
                z = 5;
                foreach (Activities oRstAct in lstActivities)
                {
                    endColNo2 = startColNo2 + 1;
                    endColName2 = Convert.ToChar(endColNo2).ToString() + "3";

                    endColNo = startColNo + 1;
                    endColName = Convert.ToChar(endColNo).ToString() + "4";

                    oSheet.Cells[3, z] = ActNo;
                    oSheet.Cells[4, z] = oRstAct.actv_name;

                    oSheet.get_Range(startColName, endColName).Font.Bold = true;
                    oSheet.get_Range(startColName, endColName).Orientation = 90;
                    oSheet.get_Range(startColName, endColName).RowHeight = 200;
                    oSheet.get_Range(startColName, endColName).WrapText = true;
                    oSheet.get_Range(startColName, endColName).Merge();
                    oSheet.get_Range(startColName, endColName).Borders.Weight = 1;

                    oSheet.get_Range(startColName2, endColName2).Merge();
                    oSheet.get_Range(startColName2, endColName2).Font.Bold = true;
                    oSheet.get_Range(startColName2, endColName2).HorizontalAlignment = HorizontalAlignment.Center;
                    oSheet.get_Range(startColName2, endColName2).Borders.Weight = 1;


                    startColNo = startColNo + 2;
                    startColName = Convert.ToChar(startColNo).ToString() + "4";

                    startColNo2 = startColNo2 + 2;
                    startColName2 = Convert.ToChar(startColNo2).ToString() + "3";
                    z = z + 2;

                    ActNo = ActNo + 1;
                }

                // --- Print Impacts , Magnitude, Imports
                // --- IMPACTS ---
                startColNo = 69;
                startColName = Convert.ToChar(startColNo).ToString() + "5";

                string srtCatName, endCatName, srtSubName, endSubName, srtImpColNoName, endImpColNoName, srtImpColName, endImpColName;

                int srtCatNo, endCatNo, srtSubNo;
                int LineNO = 1;
                int rowNo = 5;

                int GrandTotal = 0;

                string CatName = lstcmpt[0].cmpt_mainCat.ToString();
                oSheet.Cells[5, 1] = CatName;

                srtCatNo = 5;
                srtSubNo = 5;
                string SubName = lstcmpt[0].cmpt_name.ToString();
                oSheet.Cells[5, 2] = SubName;
                int x = 0;
                foreach (Components oRstImpacts in lstcmpt)
                {
                    z = 5;

                    if (CatName != oRstImpacts.cmpt_mainCat)
                    {
                        srtCatNo = rowNo;
                        CatName = oRstImpacts.cmpt_mainCat;
                        oSheet.Cells[rowNo, 1] = CatName;
                    }

                    if (SubName != oRstImpacts.cmpt_name)
                    {
                        srtSubNo = rowNo;
                        SubName = oRstImpacts.cmpt_name;
                        oSheet.Cells[rowNo, 2] = SubName;
                    }

                    startColNo = 69;
                    startColName = Convert.ToChar(startColNo).ToString() + rowNo;

                    endColNo = startColNo + 1;
                    endColName = Convert.ToChar(endColNo).ToString() + rowNo;

                    oSheet.Cells[rowNo, 4] = lstImpacts[x].impt_name;
                    x++;
                    oSheet.Cells[rowNo, 3] = LineNO;
                    srtImpColNoName = "C" + rowNo;
                    endImpColNoName = "C" + (rowNo + 1);

                    srtImpColName = "D" + rowNo;
                    endImpColName = "D" + (rowNo + 1);

                    oSheet.get_Range(srtImpColNoName, endImpColNoName).Merge();
                    oSheet.get_Range(srtImpColName, endImpColName).Merge();


                    oSheet.get_Range("A" + srtCatNo, "A" + (rowNo + 1)).Merge();

                    oSheet.get_Range("B" + srtSubNo, "B" + (rowNo + 1)).Merge();


                    int Total = 0;

                    // --- MATRIX DATA ----
                    foreach (Activities oRstAct in lstActivities)
                    {
                        endColNo = startColNo + 1;
                        endColName = Convert.ToChar(endColNo).ToString() + rowNo;

                        var filter = (from i in lstMatrice
                                      where i.actv_reference == oRstAct.actv_reference && i.impt_reference == lstImpacts[0].impt_reference
                                      select i).ToList();

                        if (filter.Count() > 0)
                        {
                            //  oSheet.Cells[rowNo, z] = filter[0].mtrx_magnitude;
                            //  oSheet.Cells[rowNo + 1, z + 1] = filter[0].mtrx_importance;

                            Total = Total + (int)(filter[0].mtrx_magnitude * filter[0].mtrx_importance);
                        }

                        oSheet.get_Range(startColName, endColName).ColumnWidth = 2.5;

                        DrawCellBorders(rowNo, z + 1, rowNo, z + 1, true, false, false, true, true);
                        DrawCellBorders(rowNo + 1, z, rowNo + 1, z, false, true, true, false, true);
                        DrawCellBorders(rowNo + 1, z + 1, rowNo + 1, z + 1, false, true, false, true, false);

                        startColNo = endColNo + 1;
                        startColName = Convert.ToChar(startColNo).ToString() + rowNo;

                        z = z + 2;
                    }

                    //  oSheet.Cells[rowNo, z] = Total;
                    endColName = Convert.ToChar(startColNo) + (rowNo + 1).ToString();
                    oSheet.get_Range(startColName, endColName).Merge();


                    GrandTotal = GrandTotal + Total;
                    rowNo = rowNo + 2;
                    LineNO = LineNO + 1;
                }
                // oSheet.Cells[4, z] = "Total";
                // oSheet.Cells[rowNo, z] = GrandTotal;

                // -----------------------------------Calculate Weighted Avg------------------------------------------
                decimal WAGrandTotal = 0;
                decimal CatWiseWATotal = 0;
                int catStrLNo = 5;

                // oSheet.Cells[4, z + 1] = "Weighted Average";

                for (int Lno = 5; Lno <= rowNo - 1; Lno++)
                {
                    var cell = (Excel.Range)oSheet.Cells[Lno, z];

                    if (Convert.ToString(cell.Value2) != null)
                    {
                        //  oSheet.Cells[Lno, z + 1] = (decimal)GrandTotal / (int)cell.Value2;
                        // WAGrandTotal = WAGrandTotal + (decimal)GrandTotal / (int)cell.Value2;

                        // -- Calculate Total Weighted Avg --
                        CatWiseWATotal = CatWiseWATotal + ((decimal)GrandTotal / (int)cell.Value2);

                        // oSheet.Cells[catStrLNo, z + 2] = CatWiseWATotal;

                        if (((Convert.ToString(((Excel.Range)oSheet.Cells[Lno + 2, 1]).Value2)) != null) && (Lno != 5))
                        {
                            catStrLNo = Lno + 2;
                            CatWiseWATotal = 0;
                        }
                    }
                }
                //oSheet.Cells[catStrLNo, z + 2] = CatWiseWATotal;
                // oSheet.Cells[rowNo, z + 1] = WAGrandTotal;


                oXL.Visible = true;
                oXL.UserControl = true;
                int sJobNumber = 1;

                oSheet.Cells.Locked = false;
                oSheet.get_Range("A2", "B2").Locked = true;
                oSheet.Protect(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                String date = DateTime.Now.ToShortDateString();
                String date1 = DateTime.Now.ToLongTimeString();
                String date2 = DateTime.Now.ToLongTimeString();
                  filePath = @"D:\cecbTemp\" + prj.proj_name + "-" + prj.proj_reference;
               
                bool folderExists = Directory.Exists(filePath);
                if (!folderExists)
                {
                    Directory.CreateDirectory(filePath);

                }

                    oWB.SaveCopyAs( filePath+@"\"+ mem.mem_name +  ".xls");
                    MessageBox.Show("Excel File saved succsessfully", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
                
            }
            catch (Exception)
            {
                throw;

            }
        
        }

        private void DrawCellBorders(int frmrowNo, int frmColNo, int TorowNo, int ToColNo, bool top, bool bottom, bool left, bool right, bool diagup)
        {
            Excel.Borders b = null;
            Excel.Borders fb = null;

            b = (Excel.Borders)(oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;
            fb = (oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;


            if (diagup == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight;
            }
            if (top == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight;
            }
            if (bottom == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight;
            }
            if (left == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight;
            }

            if (right == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight;
            }

        }

        #endregion

        private void button3_Click(object sender, RoutedEventArgs e)
        {

            try
            {
                
             Process.Start(filePath);
            }
            catch (Exception)
            {
                MessageBox.Show("Select a Project and member","Message",MessageBoxButton.OK,MessageBoxImage.Information);
               
            }
        }

        private void buttonSendAllEmail_Click(object sender, RoutedEventArgs e)
        {
            buttonSendAllEmail.IsEnabled = false;
            this.Cursor = Cursors.Wait;
            String from = "sliititb1@gmail.com";
            String to = "gayashan.gzn@gmail.com";
            String cc = "";
            String displayName = "CECB";
            String password = "900760922v";
            String Subjet = "Informing Activation Code";
            String Body = "   Activation Code: ";

            List<Members> memList = (List<Members>)listBoxMembers.ItemsSource;

            try
            {
                int mcount = 0;
                foreach (Members _mem in memList)
                {
                    try
                    {
                        //buttonSendAllEmail.Content = "Sending " +mcount+1;
                        String memMail = _mem.MemberEmails.First().mem_email.ToString();
                        bool mailSent = new cecbClasses.Data_Adapters.ActivationCodeEmail().genarateEmailToAll(from, memMail, cc, displayName, password, Subjet, Body, filePath, _mem.mem_name);
                         
                            mcount++;
                       
                    }
                    catch (Exception)
                    {
                        MessageBox.Show(_mem.mem_name+ "'s Email Not Found", "Message", MessageBoxButton.OK, MessageBoxImage.Error);
               
                        break;
                    }
                   
                   
                   
                    
                }
                MessageBox.Show(mcount + "  Emails sent Succesfully", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            catch (Exception)
            {
                
                throw;
            }
                
            

            this.Cursor = Cursors.Arrow;
            buttonSendAllEmail.IsEnabled = true;
            buttonSendAllEmail.Content = "Send Email ";
        }



    }
}
